from flask import Blueprint, request, jsonify, current_app
from datetime import datetime, timedelta
import json
from app.utils.mysql_db import get_db_connection, get_db_cursor, DatabaseLogger
from app.utils.logger import FlinkLogger, log_api_call, log_database_operation


bp = Blueprint("alerts", __name__)


@bp.get("")
@log_api_call("alerts.list_alerts")
def list_alerts():
    """获取告警列表，支持多维度过滤和搜索"""
    current_app.logger.info("Starting to fetch alerts list")
    
    # 获取查询参数
    job_name = request.args.get("job_name")
    page = request.args.get("page", 1, type=int)
    size = request.args.get("size", 20, type=int)
    status = request.args.get("status")
    alert_type = request.args.get("alert_type")
    severity = request.args.get("severity")
    time_range = request.args.get("time_range")  # today, week, month
    sort_by = request.args.get("sort_by", "sent_time")
    sort_order = request.args.get("sort_order", "DESC")
    search = request.args.get("search")  # 全文搜索
    
    # 记录查询参数
    FlinkLogger.log_business_operation(
        operation="alerts.list_alerts.params",
        details={
            "job_name": job_name,
            "page": page,
            "size": size,
            "status": status,
            "alert_type": alert_type,
            "time_range": time_range,
            "sort_by": sort_by,
            "sort_order": sort_order,
            "search": bool(search)
        },
        success=True
    )

    where = []
    params = []
    
    # 作业名称过滤
    if job_name:
        where.append("job_name LIKE %s")
        params.append(f"%{job_name}%")
    
    # 告警类型过滤
    if alert_type:
        where.append("alert_type = %s")
        params.append(alert_type)
    
    # 严重程度过滤（新增）
    if severity:
        where.append("severity = %s")
        params.append(severity)
    
    # 状态过滤
    if status:
        where.append("status = %s")
        params.append(status)
    
    # 时间范围过滤
    if time_range:
        if time_range == "today":
            where.append("DATE(sent_time) = CURDATE()")
        elif time_range == "week":
            where.append("sent_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)")
        elif time_range == "month":
            where.append("sent_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)")
    
    # 全文搜索
    if search:
        where.append("(alert_title LIKE %s OR alert_content LIKE %s OR job_name LIKE %s)")
        search_term = f"%{search}%"
        params.extend([search_term, search_term, search_term])
    
    where_sql = (" WHERE " + " AND ".join(where)) if where else ""

    # 排序字段验证（扩展支持更多时间字段）
    valid_sort_fields = [
        "sent_time",           # 创建时间（对应前端 creation_time）
        "starts_at",           # 告警开始时间
        "ends_at",             # 告警结束时间
        "resolved_time",       # 解决时间
        "last_send_time",      # 最后发送时间
        "status", 
        "alert_type", 
        "job_name", 
        "send_count"
    ]
    if sort_by not in valid_sort_fields:
        sort_by = "sent_time"
    
    if sort_order.upper() not in ["ASC", "DESC"]:
        sort_order = "DESC"

    # 查询字段（包含新增的Alertmanager兼容字段）
    select_clause = """
        id, job_id, job_name, alert_type, alert_title, alert_content as message, status, 
        sent_time as creation_time, resolved_time, send_count, last_send_time,
        escalation_reason, silence_until, can_escalate, recovery_attempts, max_recovery_attempts,
        alert_id, group_key, alertname, instance, severity, hostname, service, device, cluster_id,
        labels, annotations, starts_at, ends_at,
        is_processed, processed_by, processed_at, notification_channels
    """
    
    # 显示字段配置（包含新增字段）
    display_fields = [
        {"value": "id", "label": "ID", "sortable": True},
        {"value": "job_id", "label": "作业ID"},
        {"value": "job_name", "label": "作业名称", "sortable": True},
        {"value": "alert_type", "label": "告警类型", "sortable": True},
        {"value": "alert_title", "label": "告警标题"},
        {"value": "message", "label": "告警信息"},
        {"value": "status", "label": "状态", "sortable": True},
        {"value": "severity", "label": "严重程度", "sortable": True},
        {"value": "alertname", "label": "告警名称"},
        {"value": "instance", "label": "实例地址"},
        {"value": "hostname", "label": "主机名"},
        {"value": "service", "label": "服务名称"},
        {"value": "device", "label": "设备名称"},
        {"value": "cluster_id", "label": "集群ID"},
        {"value": "creation_time", "label": "创建时间", "sortable": True},
        {"value": "starts_at", "label": "告警开始时间", "sortable": True},
        {"value": "ends_at", "label": "告警结束时间"},
        {"value": "resolved_time", "label": "解决时间", "sortable": True},
        {"value": "send_count", "label": "发送次数", "sortable": True},
        {"value": "last_send_time", "label": "最后发送时间"},
        {"value": "escalation_reason", "label": "升级原因"},
        {"value": "silence_until", "label": "静默截止时间"},
        {"value": "is_processed", "label": "是否已处理"},
        {"value": "processed_by", "label": "处理人"},
        {"value": "processed_at", "label": "处理时间"},
        {"value": "notification_channels", "label": "通知渠道"}
    ]

    # 初始化变量
    total = 0
    status_stats = {}
    total_all = 0
    rows = []
    
    try:
        with get_db_cursor() as cur:
            # 获取总数（考虑筛选条件）
            count_sql = f"SELECT COUNT(*) as total FROM flink_cluster_alert_history {where_sql}"
            current_app.logger.debug(f"Executing count query: {count_sql}")
            cur.execute(count_sql, params)
            total_result = cur.fetchone()
            total = total_result['total'] if total_result else 0
            
            # 查询全局状态统计（不考虑筛选条件，用于统计卡片）
            cur.execute(
                """
                SELECT status, COUNT(*) as count
                FROM flink_cluster_alert_history
                GROUP BY status
                """
            )
            status_stats_rows = cur.fetchall()
            status_stats = {row['status']: int(row['count']) for row in status_stats_rows}
            total_all = sum(status_stats.values())
            
            # 获取分页数据
            offset = (page - 1) * size
            query_sql = f"""
                SELECT {select_clause}
                FROM flink_cluster_alert_history
                {where_sql}
                ORDER BY {sort_by} {sort_order}
                LIMIT %s OFFSET %s
            """
            current_app.logger.debug(f"Executing data query with offset {offset}, limit {size}")
            cur.execute(query_sql, (*params, size, offset))
            rows = cur.fetchall()
            
            # 格式化数据
            for row in rows:
                # 格式化时间字段
                if row.get('creation_time'):
                    row['creation_time'] = row['creation_time'].strftime('%Y-%m-%d %H:%M:%S')
                if row.get('resolved_time'):
                    row['resolved_time'] = row['resolved_time'].strftime('%Y-%m-%d %H:%M:%S')
                if row.get('last_send_time'):
                    row['last_send_time'] = row['last_send_time'].strftime('%Y-%m-%d %H:%M:%S')
                if row.get('silence_until'):
                    row['silence_until'] = row['silence_until'].strftime('%Y-%m-%d %H:%M:%S')
                # 新增时间字段格式化
                if row.get('starts_at'):
                    row['starts_at'] = row['starts_at'].strftime('%Y-%m-%d %H:%M:%S')
                if row.get('ends_at'):
                    row['ends_at'] = row['ends_at'].strftime('%Y-%m-%d %H:%M:%S')
                if row.get('processed_at'):
                    row['processed_at'] = row['processed_at'].strftime('%Y-%m-%d %H:%M:%S')
                # 格式化JSON字段
                if row.get('labels') and isinstance(row['labels'], str):
                    try:
                        row['labels'] = json.loads(row['labels'])
                    except:
                        pass
                if row.get('annotations') and isinstance(row['annotations'], str):
                    try:
                        row['annotations'] = json.loads(row['annotations'])
                    except:
                        pass
            
            # 记录查询结果
            FlinkLogger.log_business_operation(
                operation="alerts.list_alerts.result",
                details={
                    "total_alerts": total,
                    "returned_rows": len(rows),
                    "page": page,
                    "size": size
                },
                success=True
            )
            
            current_app.logger.info(f"Successfully fetched {len(rows)} alerts out of {total} total")
            
        return {
            "rows": rows, 
            "total": total,  # 当前筛选条件下的总数
            "total_all": total_all,  # 全局总数（用于统计卡片）
            "status_stats": status_stats,  # 全局状态统计（用于统计卡片）
            "page": page, 
            "size": size, 
            "fields": display_fields,
            "sort_by": sort_by,
            "sort_order": sort_order
        }
    except Exception as e:
        FlinkLogger.log_error(
            error=e,
            context="alerts.list_alerts",
            extra_data={
                "query_params": {
                    "job_name": job_name,
                    "page": page,
                    "size": size,
                    "status": status
                }
            }
        )
        current_app.logger.error(f"Error fetching alerts list: {str(e)}")
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500


@bp.get("/<int:alert_id>")
@log_api_call("alerts.get_alert_detail")
def get_alert_detail(alert_id: int):
    """获取告警详情"""
    current_app.logger.info(f"Fetching alert detail for alert_id: {alert_id}")
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 获取告警详情
            cur.execute("""
                SELECT 
                    id, job_id, job_name, alert_type, alert_title, alert_content, status,
                    sent_time, resolved_time, send_count, last_send_time,
                    escalation_reason, silence_until, can_escalate, 
                    recovery_attempts, max_recovery_attempts
                FROM flink_cluster_alert_history 
                WHERE id = %s
            """, (alert_id,))
            
            alert = cur.fetchone()
            if not alert:
                return {"success": False, "error": "Alert not found", "code": "NOT_FOUND"}, 404
            
            # 格式化时间字段
            if alert.get('sent_time'):
                alert['sent_time'] = alert['sent_time'].strftime('%Y-%m-%d %H:%M:%S')
            if alert.get('resolved_time'):
                alert['resolved_time'] = alert['resolved_time'].strftime('%Y-%m-%d %H:%M:%S')
            if alert.get('last_send_time'):
                alert['last_send_time'] = alert['last_send_time'].strftime('%Y-%m-%d %H:%M:%S')
            if alert.get('silence_until'):
                alert['silence_until'] = alert['silence_until'].strftime('%Y-%m-%d %H:%M:%S')
            
            # 获取相关告警历史
            cur.execute("""
                SELECT id, alert_type, sent_time, status
                FROM flink_cluster_alert_history 
                WHERE job_name = %s AND id != %s
                ORDER BY sent_time DESC 
                LIMIT 10
            """, (alert['job_name'], alert_id))
            
            related_alerts = cur.fetchall()
            for related in related_alerts:
                if related.get('sent_time'):
                    related['sent_time'] = related['sent_time'].strftime('%Y-%m-%d %H:%M:%S')
            
            alert['related_alerts'] = related_alerts
            
            return {"alert": alert}
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.get("/stats")
def get_alert_stats():
    """获取告警统计信息"""
    time_range = request.args.get("time_range", "week")  # today, week, month, all
    alert_types_param = request.args.get("alert_types", "")  # 逗号分隔的告警类型列表
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 时间过滤条件
            time_filter = ""
            if time_range == "today":
                time_filter = "WHERE DATE(sent_time) = CURDATE()"
            elif time_range == "week":
                time_filter = "WHERE sent_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)"
            elif time_range == "month":
                time_filter = "WHERE sent_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
            
            # 告警类型过滤条件
            alert_type_filter = ""
            if alert_types_param:
                # 解析逗号分隔的告警类型，并构建IN子句
                types_list = [t.strip() for t in alert_types_param.split(',') if t.strip()]
                if types_list:
                    types_str = "', '".join(types_list)
                    alert_type_filter = f"AND alert_type IN ('{types_str}')"
            
            # 组合WHERE子句
            where_clause = time_filter
            if alert_type_filter:
                if where_clause:
                    where_clause += " " + alert_type_filter
                else:
                    where_clause = "WHERE " + alert_type_filter.replace("AND ", "")
            
            # 基础统计
            cur.execute(f"""
                SELECT 
                    COUNT(*) as total_alerts,
                    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_alerts,
                    SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved_alerts,
                    SUM(CASE WHEN status = 'ESCALATED' THEN 1 ELSE 0 END) as escalated_alerts,
                    SUM(CASE WHEN status = 'SILENCED' THEN 1 ELSE 0 END) as silenced_alerts,
                    AVG(CASE WHEN resolved_time IS NOT NULL 
                        THEN TIMESTAMPDIFF(MINUTE, sent_time, resolved_time) 
                        ELSE NULL END) as avg_resolution_time_minutes
                FROM flink_cluster_alert_history 
                {where_clause if where_clause else ""}
            """)
            basic_stats = cur.fetchone()
            
            # 按告警类型统计
            cur.execute(f"""
                SELECT alert_type, COUNT(*) as count
                FROM flink_cluster_alert_history 
                {where_clause if where_clause else ""}
                GROUP BY alert_type
                ORDER BY count DESC
            """)
            type_stats = cur.fetchall()
            
            # 按严重程度统计（新增）
            cur.execute(f"""
                SELECT severity, COUNT(*) as count
                FROM flink_cluster_alert_history 
                {where_clause if where_clause else ""}
                GROUP BY severity
                ORDER BY FIELD(severity, 'emergency', 'critical', 'exception', 'warning', 'info')
            """)
            severity_stats = cur.fetchall()
            
            # 按作业统计
            cur.execute(f"""
                SELECT 
                    job_name, 
                    COUNT(*) as total_alerts,
                    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_alerts
                FROM flink_cluster_alert_history 
                {where_clause if where_clause else ""}
                GROUP BY job_name
                ORDER BY total_alerts DESC
                LIMIT 10
            """)
            job_stats = cur.fetchall()
            
            # 按小时统计（用于趋势图）
            cur.execute(f"""
                SELECT 
                    DATE_FORMAT(sent_time, '%Y-%m-%d %H:00:00') as hour,
                    COUNT(*) as count
                FROM flink_cluster_alert_history 
                {where_clause if where_clause else ""}
                GROUP BY DATE_FORMAT(sent_time, '%Y-%m-%d %H:00:00')
                ORDER BY hour
            """)
            hourly_stats = cur.fetchall()
            
            # 解决率计算
            resolution_rate = 0
            if basic_stats['total_alerts'] > 0:
                resolution_rate = round(
                    (basic_stats['resolved_alerts'] / basic_stats['total_alerts']) * 100, 2
                )
            
            return {
                "basic_stats": {
                    **basic_stats,
                    "resolution_rate": resolution_rate,
                    "avg_resolution_time_hours": round(basic_stats['avg_resolution_time_minutes'] / 60, 2) 
                        if basic_stats['avg_resolution_time_minutes'] else 0
                },
                "type_distribution": type_stats,
                "severity_distribution": severity_stats,  # 新增严重程度分布
                "job_ranking": job_stats,
                "hourly_trend": hourly_stats,
                "time_range": time_range,
                "alert_types_filter": alert_types_param  # 返回过滤条件
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.get("/trends")
def get_alert_trends():
    """获取告警趋势分析"""
    period = request.args.get("period", "daily")  # hourly, daily, weekly
    days = request.args.get("days", 7, type=int)
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 根据period选择时间分组方式
            if period == "hourly":
                group_format = "DATE_FORMAT(sent_time, '%%Y-%%m-%%d %%H:00:00')"
            elif period == "weekly":
                group_format = "YEARWEEK(sent_time)"
            else:  # daily
                group_format = "DATE(sent_time)"
            
            # 构建SQL查询（使用字符串拼接避免f-string中的%冲突）
            # 只统计Flink job类型的告警，排除Alertmanager类型
            sql = """
                SELECT 
                    {} as time_period,
                    COUNT(*) as total_count,
                    SUM(CASE WHEN alert_type = 'FIRST_FAILURE' THEN 1 ELSE 0 END) as first_failures,
                    SUM(CASE WHEN alert_type = 'RETRY_FAILURE' THEN 1 ELSE 0 END) as retry_failures,
                    SUM(CASE WHEN alert_type = 'FINAL_FAILURE' THEN 1 ELSE 0 END) as final_failures,
                    SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved_count
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                    AND alert_type IN ('FIRST_FAILURE', 'RETRY_FAILURE', 'FINAL_FAILURE', 'RECOVERED', 'FIRING', 'RESOLVED')
                GROUP BY {}
                ORDER BY time_period
            """.format(group_format, group_format)
            
            # 趋势数据
            cur.execute(sql, (days,))
            
            trends = cur.fetchall()
            
            return {
                "success": True,
                "message": "ok",
                "data": {
                    "trends": trends,
                    "period": period,
                    "days": days
                }
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.post("/<int:alert_id>/escalate")
def alert_escalate(alert_id: int):
    reason = request.json.get("reason") if request.is_json else (request.form.get("reason") or "manual escalate")
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE flink_cluster_alert_history
                SET status='ESCALATED', escalation_reason=%s
                WHERE id=%s
                """,
                (reason, alert_id),
            )
            conn.commit()
        return {"success": True, "message": "escalated"}
    finally:
        conn.close()


@bp.post("/<int:alert_id>/increment-send")
def alert_increment_send(alert_id: int):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE flink_cluster_alert_history
                SET send_count = send_count + 1, last_send_time=NOW()
                WHERE id=%s
                """,
                (alert_id,),
            )
            conn.commit()
        return {"success": True, "message": "+1"}
    finally:
        conn.close()


@bp.post("/<int:alert_id>/reset-send")
def alert_reset_send(alert_id: int):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE flink_cluster_alert_history
                SET send_count = 0, last_send_time=NULL
                WHERE id=%s
                """,
                (alert_id,),
            )
            conn.commit()
        return {"success": True, "message": "reset"}
    finally:
        conn.close()


@bp.post("/<int:alert_id>/resolve")
@log_api_call("alerts.alert_resolve")
def alert_resolve(alert_id: int):
    """解决告警"""
    current_app.logger.info(f"Attempting to resolve alert_id: {alert_id}")
    data = request.get_json() if request.is_json else {}
    resolution_reason = data.get("resolution_reason", "手动解决")
    resolved_by = data.get("resolved_by", "系统用户")
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 检查告警是否存在
            cur.execute("SELECT id, status FROM flink_cluster_alert_history WHERE id = %s", (alert_id,))
            alert = cur.fetchone()
            if not alert:
                return {"success": False, "error": "Alert not found", "code": "NOT_FOUND"}, 404
            
            if alert['status'] == 'RESOLVED':
                return {"success": False, "error": "Alert already resolved", "code": "ALREADY_RESOLVED"}, 400
            
            # 更新告警状态
            cur.execute("""
                UPDATE flink_cluster_alert_history
                SET status = 'RESOLVED', 
                    resolved_time = NOW(),
                    escalation_reason = %s
                WHERE id = %s
            """, (f"手动解决: {resolution_reason} (操作人: {resolved_by})", alert_id))
            
            conn.commit()
            
            return {
                "message": "resolved", 
                "resolution_reason": resolution_reason,
                "resolved_by": resolved_by
            }
            
    except Exception as e:
        conn.rollback()
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.post("/<int:alert_id>/silence")
def alert_silence(alert_id: int):
    """静默告警"""
    data = request.get_json() if request.is_json else {}
    minutes = int(data.get("minutes", 60))
    silence_reason = data.get("silence_reason", "手动静默")
    silenced_by = data.get("silenced_by", "系统用户")
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 检查告警是否存在
            cur.execute("SELECT id, status FROM flink_cluster_alert_history WHERE id = %s", (alert_id,))
            alert = cur.fetchone()
            if not alert:
                return {"success": False, "error": "Alert not found", "code": "NOT_FOUND"}, 404
            
            # 计算静默截止时间
            silence_until = datetime.now() + timedelta(minutes=minutes)
            
            # 更新告警状态
            cur.execute("""
                UPDATE flink_cluster_alert_history
                SET status = 'SILENCED', 
                    silence_until = %s,
                    escalation_reason = %s
                WHERE id = %s
            """, (
                silence_until.strftime('%Y-%m-%d %H:%M:%S'),
                f"手动静默: {silence_reason} (操作人: {silenced_by}, 时长: {minutes}分钟)",
                alert_id
            ))
            
            conn.commit()
            
            return {
                "message": "silenced", 
                "minutes": minutes,
                "silence_until": silence_until.strftime('%Y-%m-%d %H:%M:%S'),
                "silence_reason": silence_reason,
                "silenced_by": silenced_by
            }
            
    except Exception as e:
        conn.rollback()
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.post("/<int:alert_id>/delete")
def alert_delete(alert_id: int):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("DELETE FROM flink_cluster_alert_history WHERE id=%s", (alert_id,))
            conn.commit()
        return {"success": True, "message": "deleted"}
    except Exception as e:
        conn.rollback()
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.post("/clear-all")
def alerts_clear_all():
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("TRUNCATE TABLE flink_cluster_alert_history")
            conn.commit()
        return {"success": True, "message": "cleared"}
    except Exception as e:
        conn.rollback()
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


